﻿using GodSystemModel;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using GodSystemCommon;
using System.Data;

namespace GodSystemDAL
{
    public class UsersDAL
    {

        
        public int UpdateUserInfo(UsersModel user)
        {
            int length = 4;
            string fields = " Status = @Status,Sex = @Sex,Department = @Department ";
            if (!string.IsNullOrEmpty(user.Password))
            {
                length++;
                fields += ",Password = @Password ";
            }
            string sql = "update users set " + fields + " where Id = @Id";
            MySqlParameter[] pms = new MySqlParameter[length];
            pms[0] = new MySqlParameter("@Status", user.Status);
            pms[1] = new MySqlParameter("@Sex", user.Sex);
            pms[2] = new MySqlParameter("@Department", user.Department);
            pms[3] = new MySqlParameter("@Id", user.Id);
            if (length > 4)
            {
                pms[4] = new MySqlParameter("@Password", user.Password);
            }
            return SqlHelper.ExecuteNonQuery(sql, pms);

        }
        public object GetUserCount(string name = null)
        {
            string where = " where 1";
            bool isWhere = false;
            MySqlParameter[] pms = new MySqlParameter[1];
            if (!string.IsNullOrEmpty(name))
            {
                where += " and Name like @Name ";
                pms[0] = new MySqlParameter("@Name", "%" + name + "%");
                isWhere = true;
            }
            string sql = "select count(*) as total from Users " + where;
            if (isWhere)
            {
                return SqlHelper.ExecuteScalar(sql, pms);
            }
            else
            {
                return SqlHelper.ExecuteScalar(sql);
            }
            
        }
        public object CheckUserNameCount(string name)
        {
            string sql = "select count(*) as total from Users where Name = @Name";
            MySqlParameter[] pms =
            {
                new MySqlParameter("@Name", name)
            };
            return SqlHelper.ExecuteScalar(sql, pms);
        }

        public int Adduser(UsersModel user)
        {
            string sql = "insert into Users(Name,Password,CreateTime, Sex,Salt,Department,IsSuperManager)" +
                " values(@Name,@Password,@CreateTime, @Sex,@Salt,@Department,@IsSuperManager)";
            string salt = "";
            int index = 0;
            for (int i = 0; i <4; i++)
            {
                Random rand = new Random();
                index = Convert.ToInt32(rand.Next(0, 61));
                salt += GlobalData.RandomString[index];
            }
            string password = Utility.GetMd5FromString(user.Password + salt);
            MySqlParameter[] pms =
            {
                new MySqlParameter("@Name", user.Name),
                new MySqlParameter("@Password", password),
                new MySqlParameter("@CreateTime", DateTime.Now.ToLocalTime()),
                new MySqlParameter("@Sex", user.Sex),
                new MySqlParameter("@Salt", salt),
                new MySqlParameter("@Department", user.Department),
                new MySqlParameter("@IsSuperManager", user.IsSuperManager),
            };
            return SqlHelper.ExecuteNonQuery(sql, pms);
        }




        /// <summary>
        /// 获取用户列表
        /// </summary>
        /// <param name="currentPage"></param>
        /// <param name="pageSize"></param>
        /// <param name="userName"></param>
        /// <returns></returns>
        public DataTable GetUserList(int currentPage, int pageSize = 4, string name = null)
        {
            int startNum = (currentPage - 1) * pageSize;
            int paramLength = 2;
            if (!string.IsNullOrEmpty(name))
            {
                paramLength = 3;
            }
            MySqlParameter[] pms = new MySqlParameter[paramLength];
            pms[0] = new MySqlParameter("@startNum", startNum);
            pms[1] = new MySqlParameter("@pageSize", pageSize);
            string where = " where 1 ";
            if (!string.IsNullOrEmpty(name))
            {
                where += " and Name like @Name ";
                pms[2] = new MySqlParameter("@Name", "%" + name + "%");
            }
          


            string sql = "select Id,Name," +
                "case Status" +
                " when 1 then '正常'" +
                " when 0 then '冻结'" +
                " else '未设置' end as Status,CreateTime," +
                "case Sex" +
                " when 1 then '男'" +
                " when 0 then '女'" +
                " else '未设置' end as Sex,Department,Deadline,case IsSuperManager" +
                " when 1 then '超级管理员'" +
                " when 0 then '普通人员'" +
                " else '未设置' end as IsSuperManager from Users "+ where + " order by Id asc limit @startNum, @pageSize";
  

            return SqlHelper.ExecuteDataTable(sql, pms);
        }
        /// <summary>
        /// 检测登陆用户
        /// </summary>
        /// <param name="users"></param>
        /// <returns></returns>
        public  MySqlDataReader GetUserInfo(UsersModel users)
        {
            string sql = "select Id,Name,Status,Password,CreateTime,Sex,Salt,Department,Deadline,IsSuperManager from Users where Name = @Name limit 1";
            MySqlParameter[] pms =
            {
                new MySqlParameter("@Name", users.Name)
            };
            return SqlHelper.ExecuteReader(sql, pms);
        }


        /// <summary>
        /// 根据用户ID获取用户信息
        /// </summary>
        /// <param name="users"></param>
        /// <returns></returns>
        public MySqlDataReader GetUserInfoById(UsersModel users)
        {
            string sql = "select Id,Name,Status,Password,CreateTime,Sex,Salt,Department,Deadline,IsSuperManager from Users where Id = @Id limit 1";
            MySqlParameter[] pms =
            {
                new MySqlParameter("@Id", users.Id)
            };
            return SqlHelper.ExecuteReader(sql, pms);
        }

        /// <summary>
        /// 更新用户密码
        /// </summary>
        /// <param name="users"></param>
        /// <returns></returns>
        public int UpdateUserPassword(UsersModel users)
        {
            string sql = "update Users set Password = @Password where Id = @Id";
            MySqlParameter[] pms =
            {
                new MySqlParameter("@Password", users.Password),
                new MySqlParameter("@Id", users.Id)
            };
            return SqlHelper.ExecuteNonQuery(sql, pms);
        }

        /// <summary>
        /// 获取用户的权限列表
        /// </summary>
        /// <param name="userrolepower"></param>
        /// <returns></returns>
        public MySqlDataReader GetUserPowers(UserrolepowerModel userrolepower)
        {
            string sql = "select Id,PowerId,UserId from Userrolepower where UserId = @UserId";
            MySqlParameter[] pms =
            {
                new MySqlParameter("@UserId", userrolepower.UserId)
            };
            return SqlHelper.ExecuteReader(sql, pms);
        }

        /// <summary>
        /// 根据权限ID获取权限列表名
        /// </summary>
        /// <param name="powerIds"></param>
        /// <returns></returns>
        public MySqlDataReader GetPowers(string powerIds)
        {
            string sql = "select Id,Node from Powers where  find_in_set(Id, @powerIds)";
            MySqlParameter[] pms =
            {
                new MySqlParameter("@powerIds", powerIds)
            };
            return SqlHelper.ExecuteReader(sql, pms);
        }


        /// <summary>
        /// 根据角色ID获取角色列表名
        /// </summary>
        /// <param name="roleId"></param>
        /// <returns></returns>
        public MySqlDataReader GetRoles( string roleId)
        {
            string sql = "select Id,RoleName from Roles where find_in_set(Id, @roleId)";
            MySqlParameter[] pms =
{
                new MySqlParameter("@roleId", roleId)
            };
            return SqlHelper.ExecuteReader(sql, pms);
        }

        /// <summary>
        /// 获取角色拥有的权限列表
        /// </summary>
        /// <returns></returns>
        public MySqlDataReader GetRolepower(string roleId)
        {
            string sql = "select Id,RoleId,PowerId from Rolepower where find_in_set(Id, @roleId)";
            MySqlParameter[] pms =
{
                new MySqlParameter("@roleId", roleId)
            };
            return SqlHelper.ExecuteReader(sql, pms);
        }

        public int UpdateUserPower(int userId, List<int> powerId)
        {
            try
            {
                SqlHelper.StartTran();
                string sql = "delete from  Userrolepower  where UserId = @UserId";
                MySqlParameter[] pms =
                {
                    new MySqlParameter("@UserId", userId),
                };
                SqlHelper.ExecuteNonQueryForTran(sql, pms);
                if (powerId.Count > 0)
                {
                    string sqlInsert = "insert into Userrolepower(PowerId,UserId) values ";
                    for (int i = 0; i < powerId.Count; i++)
                    {
                        sqlInsert += "(" + powerId[i] + "," + userId + "),";
                    }
                    sqlInsert = sqlInsert.Substring(0, sqlInsert.Length - 1);
                    SqlHelper.ExecuteNonQueryForTran(sqlInsert);
                }
                SqlHelper.TransactionCommit();
                return 1;
            }catch (Exception)
            {
                SqlHelper.TransactionRollback();
                return 0;
            }


        }


        public int UpdateUserRole(int userId, List<int> roleId)
        {
            try
            {
                SqlHelper.StartTran();
                string sql = "delete from  UserRoles  where UserId = @UserId";
                MySqlParameter[] pms =
                {
                    new MySqlParameter("@UserId", userId),
                };
                SqlHelper.ExecuteNonQueryForTran(sql, pms);

                if (roleId.Count > 0)
                {
                    string sqlInsert = "insert into UserRoles(RoleId,UserId) values ";
                    for (int i = 0; i < roleId.Count; i++)
                    {
                        sqlInsert += "(" + roleId[i] + "," + userId + "),";
                    }
                    sqlInsert = sqlInsert.Substring(0, sqlInsert.Length - 1);
                    SqlHelper.ExecuteNonQueryForTran(sqlInsert);
                }
                SqlHelper.TransactionCommit();
                return 1;
            }
            catch (Exception)
            {
                SqlHelper.TransactionRollback();
                return 0;
            }

        }


    }


}
